package com.qdu.mapper;

import java.math.BigInteger;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.qdu.entity.AlluserInfo;
import com.qdu.entity.Job;
import com.qdu.entity.Usert;
import com.qdu.entity.resumefile;

public interface UsertMapper {


	@Select("select count(*) from usert where uemail=#{uemail}")
	int ifHasemail(String uemail);

	@Select("select * from Job j,bussiness b where j.bid=b.bid and (jobname LIKE '%${jobname}%' and bname LIKE '%${bname}%')")
	List<Map<String,Object>> getListByName1(@Param("jobname")String jobname,@Param("bname")String bname);	
	
	@Select("select * from Job where jobname = #{jobname} and jsalary >= #{jsalary}")
	List<Job> getListByNameandSalary(@Param("jobname")String jobname, @Param("jsalary")String jsalary);


	//进根据名称检索工作--用于首页侧边导航栏查询
	List<Job> getListByName(String jobname);



	
	@Select("select * from usert ")
	List<Usert> getAllactivUser();
	//增加新用户
	@Insert("insert into usert(uid,upwd,ugender,uemail,uphone,uinterest,uimg,ulevel,upasswordexpre,upasswordanswer) values(#{uid},#{upwd},#{ugender},#{uemail},#{uphone},#{uinterest},#{uimg},#{ulevel},#{upasswordexpre},#{upasswordanswer})")
	@Transactional//增删改添加事务管理
	void insert(Usert usert);
	
	@Insert("insert into roles values(1,#{username},'user',#{password})")
	@Transactional//增删改添加事务管理
	void insert2(@Param("username") String username,@Param("password") String password);
	
	//修改用户信息
	@Update("update usert set upwd=#{upwd},ugender=#{ugender},uemail=#{uemail},uphone=#{uphone},uinterest=#{uinterest},uimg=#{uimg},upasswordexpre=#{upasswordexpre},upasswordanswer=#{upasswordanswer},validatacode=#{validatacode},outdate=#{outdate} where uid=#{uid}")

	//@Update("update usert set upwd=#{upwd},ugender=#{ugender},uemail=#{uemail},uphone=#{uphone},uinterest=#{uinterest},uimg=#{uimg},upasswordexpre=#{upasswordexpre},upasswordanswer=#{upasswordanswer} where uid=#{uid}")
	@Transactional
	void update(Usert usert);
	
	//修改用户密码
	@Update("update usert set upwd=#{upwd} where uid=#{uid}")
	void updateUpwd(Usert usert);
	
	//用户登录
	@Select("select * from usert where uid=#{uid} and upwd=#{upwd}")
	Usert login(@Param("uid")String uid,@Param("upwd")String upwd);
	
	@Select("select * from usert where uid=#{uid} or uemail=#{uid} or uphone=#{uid}")
	Usert getOnebyuid(String uid);
	
	//通过用户id找到密保问题
	@Select("select upasswordexpre from usert where uid=#{uid}")
	Usert getMibao(String uid);
	
	//通过密保问题找回密码
	@Select("select * from usert where uid=#{uid} and upasswordexpre=#{upasswordexpre} and upasswordanswer=#{upasswordanswer}")
	Usert getPasswordbymibao(@Param("uid") String uid,@Param("upasswordexpre") String upasswordexpre,@Param("upasswordanswer") String upasswordanswer);
	
	//selectRoleNamesById 通过用户名查找他所有的角色
	@Select("select DISTINCT role from roles r,usert u,manager m,hr h "
			+ "where (r.rolename = u.uid  or r.rolename = m.mid or r.rolename = h.hid) "
			+ "AND rolename = #{username}")
	List<String> selectRoleNamesById(String username);
	//security 
	@Select("select DISTINCT rolename,rpassword from roles r,usert u,manager m,hr h "
			+ "where (r.rolename = u.uid  or r.rolename = m.mid or r.rolename = h.hid) "
			+ "AND rolename = #{uid}")
	AlluserInfo getOneByUid1(String uid);
	//通过usert表获取职业兴趣关键字：如：销售，医学等
	@Select("select DISTINCT LEFT(batchname,2) AS interest from batch")
	List<String> getInterestHad();

	//通过邮箱找到用户id
	@Select("select * from usert where uemail=#{uemail}")
	Usert getUserbyEmail(@Param("uemail")String uemail);
	
	//用户提交简历文件：上传word文档
	void updateResumeFile(String uid, String jid, MultipartFile pfile);

	//用户查询个人简历审核进度
	BigInteger getUseResumeProcess(String uid);
	
	//判断注册过程中输入的用户名是否重复
	@Select("select COUNT(*) from usert,hr,manager WHERE uid=#{uid} OR hid=#{uid} OR manager.mid=#{uid}")
	Integer isuidExist(String uid);
	
	//用户上传简历
	@Insert("insert into resumefile(uid,jid,img) values(#{uid},#{jid},#{img})")
	int insertresumefile(@Param("uid")String uid,@Param("jid")String jid,@Param("img")String img);
	
	List<Map<String,Object>> queryJobByMult(@Param("bpro")String bpro,@Param("education") String education,@Param("time") String time,@Param("salary") String salary);
	
	//l-用于获取用户所有投递过的简历
	@Select("select * from resumefile r,job j where r.uid = #{uid} and r.jid = j.jid")
	List<Map<String,Object>> getResumeByuid(String uid);

}